﻿using System;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;
using AB.ServiceDirectory.Commons.DataFilters;
using AB.ServiceDirectory.Entities;

namespace AB.ServiceDirectory.Dao
{
    /// <summary>
    /// TrustDistrictsDao class
    /// </summary>
    [Serializable]
    public class TrustDistrictsDao
    {
        [NonSerialized] private readonly ServicesDirectoryDataContext _context = DB.Context;

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <param name="filter">The filter.</param>
        /// <param name="sortType">Type of the sort.</param>
        /// <param name="startRowIndex">Start index of the row.</param>
        /// <param name="max">The max.</param>
        /// <returns></returns>
        public IQueryable<TrustDistrict> GetList(TrustDistrictFilter filter, string sortType, int startRowIndex, int max)
        {
            IQueryable<TrustDistrict> data = from d in _context.TrustDistricts
                                             select d;

            // 1. Filter by condition
            if (filter != null)
            {
                data = GetListQuery(data, filter);
            }

            // 2. Sorting
            data = GetListSort(data, sortType);

            // 3. Limit the rows
            data = data.Skip(startRowIndex).Take(max);
            return data;
        }

        /// <summary>
        /// Gets the list query.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <param name="filter">The filter.</param>
        /// <returns></returns>
        private static IQueryable<TrustDistrict> GetListQuery(IQueryable<TrustDistrict> data, TrustDistrictFilter filter)
        {
            data = data.Where(d => d.TrustRegionId == filter.TrustRegionId);

            if (filter.BeginningLetters != null)
            {
                data = data.Where(g => filter.BeginningLetters.Contains(g.TrustDistrictName[0].ToString()));
            }
            if (!filter.IncludeInactive)
            {
                data = data.Where(g => g.IsActive);
            }
            return data;
        }

        /// <summary>
        /// Gets the list sort.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <param name="sortType">Type of the sort.</param>
        /// <returns></returns>
        private static IQueryable<TrustDistrict> GetListSort(IQueryable<TrustDistrict> data, string sortType)
        {
            bool sortDescending = false;
            if (!string.IsNullOrEmpty(sortType))
            {
                string[] values = sortType.Split(' ');
                sortType = values[0];
                if (values.Length > 1)
                {
                    sortDescending = (values[1] == "DESC");
                }
                switch (sortType)
                {
                    case "TrustDistrictName":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.TrustDistrictName)
                                   : data.OrderBy(g => g.TrustDistrictName);
                        break;
                    case "Description":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.Description)
                                   : data.OrderBy(g => g.Description);
                        break;
                    case "TrustRegionName":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.TrustRegion.TrustRegionName)
                                   : data.OrderBy(g => g.TrustRegion.TrustRegionName);
                        break;
                    case "IsActive":
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.IsActive)
                                   : data.OrderBy(g => g.IsActive);
                        break;
                    default:
                        data = sortDescending
                                   ? data.OrderByDescending(g => g.TrustDistrictId)
                                   : data.OrderBy(g => g.TrustDistrictId);
                        break;
                }
            }
            return data;
        }

        /// <summary>
        /// Gets the list count.
        /// </summary>
        /// <param name="filter">The filter.</param>
        /// <returns></returns>
        public int GetListCount(TrustDistrictFilter filter)
        {
            IQueryable<TrustDistrict> data = from gor in _context.TrustDistricts
                                             select gor;
            if (filter != null)
            {
                data = GetListQuery(data, filter);
            }
            return data.Count();
        }

        /// <summary>
        /// Gets the trust districts id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public TrustDistrict GetTrustDistrictsId(int id)
        {
            var trustDistricts = _context.TrustDistricts.FirstOrDefault(g => g.TrustDistrictId == id);
            return trustDistricts;
        }

        /// <summary>
        /// Adds the trust district.
        /// </summary>
        /// <param name="newTrustDistrict">The new trust district.</param>
        /// <returns></returns>
        public bool AddTrustDistrict(TrustDistrict newTrustDistrict)
        {
            try
            {
                using (var transaction = new TransactionScope())
                {
                    _context.TrustDistricts.InsertOnSubmit(newTrustDistrict);
                    _context.SubmitChanges();
                    transaction.Complete();
                    return true;
                }
            }
            catch (SqlException exception)
            {
                Log.GetLog(this).Error(exception);
                return false;
            }
        }

        /// <summary>
        /// Edits the trust districts.
        /// </summary>
        /// <param name="newTrustDistrict">The new trust district.</param>
        /// <returns></returns>
        public bool EditTrustDistricts(TrustDistrict newTrustDistrict)
        {
            TrustDistrict oldTrustDistrict =
                _context.TrustDistricts.FirstOrDefault(d => d.TrustDistrictId == newTrustDistrict.TrustRegionId);
            if (oldTrustDistrict != null)
            {
                oldTrustDistrict.TrustDistrictName = newTrustDistrict.TrustDistrictName;
                oldTrustDistrict.Description = newTrustDistrict.Description;
                oldTrustDistrict.IsActive = newTrustDistrict.IsActive;

                _context.SubmitChanges();
                return true;
            }
            return false;
        }

        /// <summary>
        /// Checks the name of the trust district.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public bool CheckTrustDistrictName(string name)
        {
            TrustDistrict trustDistrictName =
                _context.TrustDistricts.FirstOrDefault(d => d.TrustDistrictName.Equals(name));
            return trustDistrictName != null;
        }

        /// <summary>
        /// Sets the trust district status.
        /// </summary>
        /// <param name="trustDistrictId">The trust district id.</param>
        /// <param name="status">if set to <c>true</c> [status].</param>
        /// <returns></returns>
        public bool SetTrustDistrictStatus(int trustDistrictId, bool status)
        {
            TrustDistrict trustDistrict =
                _context.TrustDistricts.FirstOrDefault(o => o.TrustDistrictId == trustDistrictId);
            if (trustDistrict != null)
            {
                trustDistrict.IsActive = status;
                _context.SubmitChanges();
                return true;
            }
            return false;
        }
    }
}